package iedcyt.acute.dataAccess.dao.acute1;


import iedcyt.acute.dataAccess.domain.acute1.Doc_Corp;
import iedcyt.acute.dataAccess.generic.SpringDao;
import iedcyt.acute.dataAccess.idao.acute1.IDoc_CorpDao;

import java.util.ArrayList;
import java.util.List;



public class Doc_CorpDao extends SpringDao implements IDoc_CorpDao  {

	@Override
	public void create() {
		//comprobamos si la tabla existe y si no la creamos
		try {
			_jdbcTemplate.queryForInt("select count(*) from Doc_Corp");
		} catch (Exception e) {
			_jdbcTemplate.execute("CREATE TABLE Doc_Corp (idDocCorp numeric, idCorp numeric, idDoc numeric, corpPosition numeric)");
			_jdbcTemplate.execute("CREATE INDEX idxDoc_Corp1 on Doc_Corp (idDocCorp) WITH PRIMARY");
			_jdbcTemplate.execute("CREATE INDEX idxDoc_Corp2 on Doc_Corp (idDoc)");
			_jdbcTemplate.execute("CREATE INDEX idxDoc_Corp3 on Doc_Corp (idCorp)");
			_jdbcTemplate.execute("CREATE UNIQUE INDEX idxDoc_Corp4 on Doc_Corp (idDocCorp, CorpPosition)");
			_jdbcTemplate.execute("ALTER TABLE Doc_Corp ADD FOREIGN KEY (idDoc) REFERENCES Document(idDoc)");
			_jdbcTemplate.execute("ALTER TABLE Doc_Corp ADD FOREIGN KEY (idCorp) REFERENCES Corp(idCorp)");
		}
	}
	
	@Override
	public List<Doc_Corp> selectAll() {
		
		return _jdbcTemplate.query("SELECT * FROM Doc_Corp",
				new Doc_CorpRowMapper());
	}
	
	
	@Override
	public void deleteAll() {
		
		_jdbcTemplate.update("DELETE from Doc_Corp");
	}

	

	@Override
	public void insert(Doc_Corp doc) {
	
		_jdbcTemplate.update("INSERT INTO Doc_Corp (idDocCorp, idCorp, idDoc, CorpPosition) VALUES (?,?,?,?)",
				new Object[] { doc.get_idDocCorp(), doc.get_idCorp(), doc.get_idDoc(), doc.get_CorpPosition() });
		
	}
	
	@Override
	public void update(Doc_Corp doc) {
	
		_jdbcTemplate.update("UPDATE Doc_Corp SET  idCorp= ?, idDoc= ?, CorpPosition = ? WHERE idDocCorp = ?",
				new Object[] { doc.get_idCorp(), doc.get_idDoc(), doc.get_CorpPosition(), doc.get_idDocCorp() });
		
	}

	

		
	@Override
	public void delete(int id) {
	
		_jdbcTemplate.update("DELETE from Doc_Corp WHERE idDocCorp=?",
				new Object[] { id  });
		
	}

	@Override
	public int selectMaxId() {
		return _jdbcTemplate.queryForInt("SELECT MAX(idDocCorp) FROM Doc_Corp");
	}

	/**
	 * @see iedcyt.acute.dataAccess.idao.acute1.IDoc_CorpDao#selectByIdDocCorp(int, java.lang.Boolean)
	 */
	@Override
	public List<Doc_Corp> selectByIdDocCorp(int id, Boolean rps_bool) {
		
		String rps = "";
		
		if (rps_bool != null) {
			if (rps_bool)
				rps = " AND CorpPosition = -1";
			else rps = " AND CorpPosition <> -1";
		}
		
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Corp WHERE idDocCorp = ?" + rps,
				new Object[] { id },
				new Doc_CorpRowMapper());
	}

	/**
	 * @see iedcyt.acute.dataAccess.idao.acute1.IDoc_CorpDao#selectByIdDoc(int, java.lang.Boolean)
	 */
	@Override
	public List<Doc_Corp> selectByIdDoc(int id, Boolean rps) {
		
		String rpsQue = "";
		
		if (rps != null) {
			if (rps)
				rpsQue = " AND CorpPosition = -1";
			else rpsQue = " AND CorpPosition <> -1";
		}
		
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Corp WHERE idDoc = ?" + rpsQue,
				new Object[] { id },
				new Doc_CorpRowMapper());
	}

	@Override
	public List<Doc_Corp> selectByIdCorp(int id, boolean withoutRps) {
		
		String rps = "";
		
		if (withoutRps)
			rps = " AND CorpPosition <> -1";
		
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Corp WHERE idCorp = ?" + rps,
				new Object[] { id },
				new Doc_CorpRowMapper());
	}
	
	@Override
	public List<Doc_Corp> selectByIdCe(int[] idsCe, boolean withoutRps) {
		
		String rps = "";
		
		if (withoutRps)
			rps = " AND docC.CorpPosition <> -1";
		
		if (idsCe.length != 0)
		{
		String where = "cenC.idCe = ? ";
		int cnt = 1;
		for (int i = 0; i < idsCe.length -1 ; i++)
		{
			cnt++;
			where += "OR cenC.idCe = ? ";
		}
		
		Object[] objs = new Object[cnt];
		
		for (int i = 0; i < idsCe.length; i++)
		{
			objs[i] = idsCe[i];
		}
		
		
		return _jdbcTemplate
		.query(
				"SELECT docC.* FROM Doc_Corp docC " +
				"INNER JOIN (Corp co  " +
					"INNER JOIN Center_Corp cenC ON cenC.idCorp = co.idCorp) " +
					"ON docC.idCorp = co.idCorp " +
				"WHERE " + where + rps,
				objs,
				new Doc_CorpRowMapper());
		}
	
		else return new ArrayList<Doc_Corp>();
	}

	@Override
	public List<Doc_Corp> selectByIdCeByIdDoc(int[] idsCe, int idDoc, boolean withoutRps) {

		String rps = "";
		
		if (withoutRps)
			rps = " AND docC.CorpPosition <> -1";
		
		if (idsCe.length != 0)
		{
			String where = "cenC.idCe in (";
			for (int i = 0; i < idsCe.length; i++) {
				where += "?,";
			}
			where += "_)";
			where = where.replaceAll(",_","");
			
			Object[] objs = new Object[idsCe.length+1];
			
			for (int i = 0; i < idsCe.length ; i++)
			{
				objs[i] = idsCe[i];
			}
			
			objs[idsCe.length] = idDoc;
			
			return _jdbcTemplate
			.query(
					"SELECT docC.* FROM Doc_Corp docC " +
					"INNER JOIN (Corp co  " +
						"INNER JOIN Center_Corp cenC ON cenC.idCorp = co.idCorp) " +
						"ON docC.idCorp = co.idCorp " +
					"WHERE " + where + rps + " AND docC.idDoc = ?",
					objs,
					new Doc_CorpRowMapper());
		} else return new ArrayList<Doc_Corp>();
	}


	@Override
	public List<Doc_Corp> selectByNumberOfCorpsInDoc(int number, boolean distinct) {
		
		String query = "";
		
		if (distinct)
		 query = "SELECT DISTINCT 0, idCorps, idDoc, 0  FROM Doc_Corp GROUP BY idDoc having COUNT(idDoc) = ? ORDER BY idDoc";
		else query = "SELECT dc1.* FROM Doc_Corp dc1 INNER JOIN (SELECT idDoc FROM Doc_Corp GROUP BY idDoc having COUNT(idDoc) = ?) dc2 ON dc1.idDoc = dc2.idDoc  ORDER BY dc1.idDoc";
		
		
		return _jdbcTemplate
		.query(
				query,
				new Object[] { number },
				new Doc_CorpRowMapper());
	}


	@Override
	public List<Doc_Corp> selectByIdDocAndCorpPos(int idDoc, int corpPos) {
		
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Corp WHERE idDoc = ? AND CorpPosition = ?",
				new Object[] { idDoc, corpPos },
				new Doc_CorpRowMapper());
	}


	@Override
	public List<Doc_Corp> selectByIdDocAndIdCorp(int[] idDoc, int idCorp) {
		
		if (idDoc.length != 0)
		{
		String where = "idDoc = ? ";
		int cnt = 1;
		for (int i = 0; i < idDoc.length -1 ; i++)
		{
			cnt++;
			where += "OR idDoc = ? ";
		}
		where += "AND idCorp = ? ";
		
		Object[] objs = new Object[cnt+1];
		
		for (int i = 0; i < idDoc.length ; i++)
		{
			objs[i] = idDoc[i];
		}
		
		objs[idDoc.length] = idCorp;
		
		return _jdbcTemplate
		.query(
				"SELECT * FROM Doc_Corp WHERE " + where,
				objs,
				new Doc_CorpRowMapper());
		}
		else return new ArrayList<Doc_Corp>();
	}


	@Override
	public List<Doc_Corp> selectNonClassify(int idDoc) {
		return _jdbcTemplate
		.query(
			"SELECT docC.idDocCorp, docC.idDoc, docC.idCorp, docC.CorpPosition FROM Doc_Corp docC " + 
			"LEFT OUTER JOIN SignClassification sC ON docC.idDocCorp = sC.idDocCorp " +  
			"WHERE docC.idDoc = ? AND sC.idDocCorp IS NULL ",// + 
			//"GROUP BY docS.idDocSign, docS.idDoc, docS.idSign, docS.SignPosition HAVING COUNT(docS.idSign) = 1",
			new Object[] { idDoc },
			new Doc_CorpRowMapper());
	}

	

	
	
}

